package com.taomeng.dao.impl;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.taomeng.dao.UserDao;
import com.taomeng.entity.PageBean;
import com.taomeng.entity.User;
import com.taomeng.entity.UserInfo;
import com.taomeng.util.JdbcTemplateSupport;
import com.taomeng.util.Md5Util;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.subject.Subject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private JdbcTemplateSupport jdbcTemplateSupport;

    @Override
    public PageBean<UserInfo> findByPage(int page, int limit) {
        StringBuilder sb = new StringBuilder("SELECT \n" +
                "\tt_user.id,\n" +
                "\tt_user.username,\n" +
                "\tt_user.name,\n" +
                "\tt_user.sex,\n" +
                "\tdepartment.name departmentName,\n" +
                "\tt_role.name roleName\n" +
                "\t\n" +
                "FROM\n" +
                "\tt_user,\n" +
                "\tdepartment,\n" +
                "\tt_user_role,\n" +
                "\tt_role\n" +
                "WHERE\n" +
                "\tt_user_role.user_id=t_user.id\n" +
                "\tand\n" +
                "\tt_user_role.role_id=t_role.id\n" +
                "\tand\n" +
                "\tt_user.departmentId=department.id\n" +
                "\t");

        Subject subject = SecurityUtils.getSubject();
        User principal = (User) subject.getPrincipal(); //获取当前用户
        if (subject.hasRole("deptadmin")) sb.append(" and t_user.departmentId = ").append(principal.getDepartmentId());
        Page<UserInfo> pagination = PageHelper.startPage(page, limit);

        /**
         * 判断模糊搜索的key是否为空，如果是空则直接进行搜索
         * 否则进行模糊搜索
         */
        try {
            return jdbcTemplateSupport.queryForPage(
                    sb.toString(),
                    pagination,
                    new BeanPropertyRowMapper<>(UserInfo.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }

    }

    @Override
    public User findByUsername(String username) {
        String sql = "select * from t_user where username = ?";
        Object[] params = {
                username
        };

        try {
            return jdbcTemplate.queryForObject(sql, params, new BeanPropertyRowMapper<>(User.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void updatePasswordByUserId(Long id, String password) {
        String sql = "update t_user set password = ? where id = ? ";
        Object[] params = {
                password,
                id
        };
        try {
            jdbcTemplate.update(sql, params);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }

    }

    @Override
    public List<User> getUsersByDeptId(int deptId) {
        String sql = "SELECT\n" +
                "\tt_user.id,\n" +
                "\tt_user.name\n" +
                "FROM\n" +
                "\tt_user,\n" +
                "\tdepartment\n" +
                "where\n" +
                "\tt_user.departmentId=department.id\n" +
                "\tand\n" +
                "\tdepartment.id=?; ";
        Object[] params = {deptId};
        try {
            return jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(User.class));
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Long add(User user) {
        String sql = "insert into t_user (username,password,name,sex,departmentId)values (?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        try {
            jdbcTemplate.update(con -> {
                PreparedStatement ps = jdbcTemplate.getDataSource()
                        .getConnection().prepareStatement(sql, new String[]{"username", "password", "name", "sex", "departmentId"});
                ps.setString(1, user.getUsername());
                ps.setString(2, Md5Util.encrypt(user.getPassword()));   //用户加盐加密10000次
                ps.setString(3, user.getName());
                ps.setByte(4, user.getSex());
                ps.setLong(5, user.getDepartmentId());
                return ps;
            }, keyHolder);
            return keyHolder.getKey().longValue();
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean addRoleByUserId(Long id, int roleId) {
        String sql = "insert into t_user_role values (?,?)";
        Object[] params = {
                id,
                roleId
        };
        try {
            return jdbcTemplate.update(sql, params) == 1;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean delRoleByUserId(Long userId) {
        String sql = "delete from t_user_role where user_id = ?";
        Object[] params = {userId};
        try {
            return jdbcTemplate.update(sql, params) == 1;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean delById(Long userId) {
        String sql = "delete from t_user where id = ?";
        Object[] params = {userId};
        try {
            return jdbcTemplate.update(sql, params) == 1;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateRoleByUserId(Long id, int roleId) {
        String sql = "update t_user_role set role_id = ? where user_id = ? ";
        Object[] params = {
                roleId,
                id
        };
        try {
            return jdbcTemplate.update(sql,params)==1;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean updateUser(User user) {
        String sql = "update t_user set username = ? , password = ? ,name = ?,sex = ?, departmentId = ? where id = ?";
        Object[] params = {
                user.getUsername(),
                Md5Util.encrypt(user.getPassword()),
                user.getName(),
                user.getSex(),
                user.getDepartmentId(),
                user.getId()
        };

        try {
            return jdbcTemplate.update(sql,params)==1;
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return false;
    }
}
